﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace QunYing.DAL
{
    public partial class LeaveMsg
    {
        public LeaveMsg() { }

        #region method
        /// <summary>
        /// 留言
        /// </summary>
        public int Add(QunYing.Model.LeaveMsg model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into LeaveMsg(");
            strSql.Append("用户ID,留言内容,类型)");
            strSql.Append(" values (");
            strSql.Append("@用户ID,@留言内容,@类型)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@用户ID", SqlDbType.NVarChar,20),
					new SqlParameter("@留言内容", SqlDbType.NVarChar,4000),
                    new SqlParameter("@类型", SqlDbType.NVarChar,20)};
            parameters[0].Value = model.用户ID;
            parameters[1].Value = model.回复内容;
            parameters[2].Value = model.类型;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 获取留言
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public DataTable GetMsg(string strID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select 用户ID,留言内容,留言时间,回复ID,回复内容,回复时间 ");
            strSql.Append(" FROM LeaveMsg where 用户ID=@用户ID order by 留言时间 desc");
            SqlParameter[] parameters = { new SqlParameter("@用户ID", strID) };
            return DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
        }

        /// <summary>
        /// 经销商获取留言信息
        /// </summary>
        /// <param name="strID">经销商编号</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="strState">是否已回复（是，否，）</param>
        /// <param name="isDealer">是否经销商</param>
        /// <returns>ID（留言的自增ID），用户ID，留言内容，留言时间</returns>
        public DataTable GetMsgByDealer(string DealerID, DateTime startDate, DateTime endDate, string strState, string strType, string isDealer)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select a.ID,b.用户姓名,a.留言内容,a.留言时间,a.回复ID,a.类型,a.回复内容,a.回复时间,b.经销商编号 from LeaveMsg a
                            left join VipInfo b on a.用户ID=b.用户ID
                            where a.类型 like '%" + strType + @"%' and CONVERT(varchar(100), a.留言时间, 23) between @开始时间 and @结束时间");
            if (isDealer == "是")
            {
                strSql.Append(" and b.经销商编号=@经销商编号");
            }
            if (strState == "是")
            {
                strSql.Append(" AND a.回复时间 is not null");
            }
            else if (strState == "否")
            {
                strSql.Append(" AND a.回复时间 is null");
            }
            strSql.Append(" order by a.留言时间 DESC");

            if (isDealer == "是")
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@经销商编号", DealerID),
                                                new SqlParameter("@开始时间", startDate),
                                                new SqlParameter("@结束时间", endDate)
                                            };
                return DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
            }
            else
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@开始时间", startDate),
                                                new SqlParameter("@结束时间", endDate)
                                            };

                return DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
            }
        }
        //        /// <summary>
        //        /// 最新未回复留言
        //        /// </summary>
        //        /// <param name="DealerID"></param>
        //        /// <param name="top"></param>
        //        /// <returns></returns>
        //        public DataTable getNewMsg(string DealerID, int top)
        //        {
        //            StringBuilder strSql = new StringBuilder();
        //            strSql.Append(@"select top "+top.ToString ()+@" a.ID,b.用户姓名,a.留言内容,a.留言时间,a.回复ID,a.回复内容,a.回复时间 from LeaveMsg a
        //                            left join VipInfo b on a.用户ID=b.用户ID
        //                            where b.经销商编号=@经销商编号 and a.回复时间 is null");
        //            strSql.Append(" order by a.留言时间 DESC");

        //            SqlParameter[] parameters = { 
        //                         new SqlParameter("@经销商编号", DealerID)
        //               };
        //            return DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
        //        }
        /// <summary>
        /// 回复留言
        /// </summary>
        /// <param name="ID">留言的自增ID</param>
        /// <param name="content">回复内容</param>
        /// <param name="DealerID">经销商编号</param>
        /// <returns></returns>
        public bool ReplyMsg(string ID, string DealerID, string content)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update LeaveMsg set 回复ID=@回复ID,回复内容=@回复内容,回复时间=getdate() where ID=@ID");
            SqlParameter[] parameters = { 
                         new SqlParameter("@回复ID", DealerID), 
                         new SqlParameter("@回复内容", content),
                         new SqlParameter("@ID", ID),
               };
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 删除留言
        /// </summary>
        /// <param name="ID">留言的自增ID</param>
        /// <returns></returns>
        public bool DelMsg(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from LeaveMsg where ID=@ID");
            SqlParameter[] parameters = {
                         new SqlParameter("@ID", ID)
               };
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }



        /// <summary>
        /// 留言统计
        /// </summary>
        /// <param name="DealerID"></param>
        /// <returns></returns>
        public DataSet QueryTotalMsg(string DealerID, DateTime startDate, DateTime endDate)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@经销商编号", DealerID),
                    new SqlParameter("@startDate", startDate),
                    new SqlParameter("@endDate", endDate)
              };
            return DbHelperSQL.RunProcedure("P_TotalMsg", parameters, "result");
        }
        #endregion
    }
}
